using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace DBConnecter
{
    static class SQLScripts
    {
        internal static void CreateHallsRowsSeats()
        {
            if (!Accept("This will delete and reset the database tables containing Hall, Row, and Seat Information"))
                return;

            using (SqlConnection DBConnection = new SqlConnection(Properties.Settings.Default.DBConnectionString))
            {
                DBConnection.Open();
                SqlCommand sql;

                sql = new SqlCommand("DELETE FROM Seat", DBConnection);
                sql.ExecuteScalar();

                sql = new SqlCommand("DELETE FROM Row;", DBConnection);
                sql.ExecuteScalar();

                sql = new SqlCommand("DELETE FROM Hall;", DBConnection);
                sql.ExecuteScalar();


                sql = new SqlCommand("DBCC CHECKIDENT('Hall', RESEED, 0);", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("DBCC CHECKIDENT('Row', RESEED, 0);", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("DBCC CHECKIDENT('Seat', RESEED, 0);", DBConnection);
                sql.ExecuteScalar();

                for (int i = 1; i < 4; i++)
                {
                    sql = new SqlCommand("INSERT INTO Hall (Number) VALUES ('" + i + "');", DBConnection);
                    sql.ExecuteScalar();
                    for (int j = 1; j < 13; j++)
                    {
                        sql = new SqlCommand("INSERT INTO Row (Number, HallID) VALUES ('" + j + "', '" + i + "');", DBConnection);
                        sql.ExecuteScalar();
                        for (int k = 1; k < 21; k++)
                        {
                            if (i == 3)
                            {
                            }
                            sql = new SqlCommand("INSERT INTO Seat (Number, Type, RowID) VALUES ('" + k + "', 'Normal', '" + (((i - 1) * 12) + j) + "');", DBConnection);
                            sql.ExecuteScalar();
                        }
                    }
                }
            }
        }

        internal static void CreateShows()
        {
            if (!Accept("This will delete and reset the database tables containing Show Information"))
                return;

            using (SqlConnection DBConnection = new SqlConnection(Properties.Settings.Default.DBConnectionString))
            {
                DBConnection.Open();
                SqlCommand sql;

                sql = new SqlCommand("DELETE FROM Show;", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("DBCC CHECKIDENT('Show', RESEED, 0);", DBConnection);
                sql.ExecuteScalar();

                sql = new SqlCommand("INSERT INTO Show (Name, Length, Time, MovieId, HallId) VALUES ('Number 23', '1:30:00', '2013-11-24 13:00:00', '1', '1')", DBConnection);
                sql.ExecuteScalar();
            }
        }

        internal static void CreateMovies()
        {
            if (!Accept("This will delete and reset the database tables containing Movie Information"))
                return;

            using (SqlConnection DBConnection = new SqlConnection(Properties.Settings.Default.DBConnectionString))
            {
                DBConnection.Open();
                SqlCommand sql;

                sql = new SqlCommand("DELETE FROM Movie;", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("DBCC CHECKIDENT('Movie', RESEED, 0);", DBConnection);
                sql.ExecuteScalar();

                sql = new SqlCommand("INSERT INTO Movie (Name, Runtime, Genre, ThirdDimensional, Language, Subtitles, Description) VALUES ('Number 23', '1:30:00', 'Thriller', '0', 'English', 'Danish', 'Walter Sparrow becomes obsessed with a novel that he believes was written about him. As his obsession increases, more and more similarities seem to arise.')", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("INSERT INTO Movie (Name, Runtime, Genre, ThirdDimensional, Language, Subtitles, Description) VALUES ('Escape Plan', '1:55:00', 'Thriller', '0', 'English', 'Danish', 'Escape Plan er en action-thriller om Ray Breslin, der specialiserer sig i et helt særligt erhverv. Han tester fængslers sikkerhed.')", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("INSERT INTO Movie (Name, Runtime, Genre, ThirdDimensional, Language, Subtitles, Description) VALUES ('Captain Phillips', '2:14:00', 'Thriller', '0', 'English', 'Danish', 'I thrilleren Captain Phillips er fragtskibet, Maersk Alabama, på vej til Mombasa i Kenya med en last på 17.000 tons, da skibet bliver bordet af en gruppe pirater 440 km ud fra den somaliske kyst.')", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("INSERT INTO Movie (Name, Runtime, Genre, ThirdDimensional, Language, Subtitles, Description) VALUES ('2 Guns', '1:49:00', 'Action', '0', 'English', 'Danish', 'I action-thrilleren 2 Guns er Trench og Stigman agenter fra forskellige specialafdelinger, der uden at kende til hinanden begge arbejder undercover for at infiltrere det samme narkokartel.')", DBConnection);
                sql.ExecuteScalar();
                sql = new SqlCommand("INSERT INTO Movie (Name, Runtime, Genre, ThirdDimensional, Language, Subtitles, Description) VALUES ('Carrie', '1:40:00', 'Gyser', '0', 'English', 'Danish', 'Carrie White er en genert pige, der lever alene sammen med sin overbeskyttende og fanatisk religiøse mor, mens hun i skolen dagligt mobbes af sine jævnaldrende og udsættes for et stigende antal ubehageligheder.')", DBConnection);
                sql.ExecuteScalar();
            }
        }

        private static bool Accept(string message)
        {
            Console.WriteLine(message);
            Console.WriteLine("Continue? [Y/N]");
            if (Console.ReadKey().KeyChar != 'Y')
            {
                return false;
            }
            return true;
        }
    }
}
